package DBLayer;

import java.sql.*;
import java.util.ArrayList;
import ModelLayer.ProductLine;
import ModelLayer.Product;

public class DbProductLine<Int> implements IFDbProductLine{
	private Connection con;

	/** Creates a new instance of DbProductLine */
	public DbProductLine() {
		con = DbConnection.getInstance().getDBcon();
	}
	// Implements the methods from the interface
			
			public ArrayList<ProductLine> getAllProductLines(boolean retrieveAssociation) {
				return miscWhere("", retrieveAssociation);
			}

			// get one ProductLine having the Id
			public ProductLine searchProductId(int productId,
					boolean retrieveAssociation) {
				String wClause = "  ProductLine Id: = '" + productId + "'";
				return singleWhere(wClause, retrieveAssociation);
			}

			// insert a new ProductLine
			public int insertProductLine(ProductLine productLn) throws Exception { 
				int nextId = generateMax.getMaxId("Select max(productId) from ProductLine");
				nextId = nextId + 1;
				System.out.println("next ID = " + nextId);

				int rc = -1;
				String query = "INSERT INTO ProductLine(productId, quantity)  VALUES('"
						+ nextId
						+ "','"
						+ productLn.getQuantity() + "','";
						
				System.out.println("insert : " + query);
				try { // insert new ProductLine 
					Statement stmt = con.createStatement();
					stmt.setQueryTimeout(5);
					rc = stmt.executeUpdate(query);
					stmt.close();
				}// end try
				catch (SQLException ex) {
					System.out.println("ProducLine is not inserted");
					throw new Exception("ProductLine is not inserted");
				}
				return (rc);
			}

			
			private ProductLine buildProductLine(ResultSet results) {
				ProductLine productLnObj = new ProductLine();
				try { // the columns from the table ProductLine are used
					productLnObj.setProductLineId(results.getInt("productId"));
					productLnObj.setQuantity(results.getInt("qunatity"));
					
				} catch (Exception e) {
					System.out.println("error in building the ProductLine object");
				}
				return productLnObj;
}
			
			public int updateProductLine(ProductLine productLn) {
				ProductLine productLnObj = productLn;
				int rc = -1;

				String query = "UPDATE ProductLine SET " + "quantity ='" + productLnObj.getQuantity() 
						+ "' " + " WHERE ProductId = '" + productLnObj.getProductLineId()
						+ "'";
				System.out.println("Update query:" + query);
				try { // update ProductLine
					Statement stmt = con.createStatement();
					stmt.setQueryTimeout(5);
					rc = stmt.executeUpdate(query);

					stmt.close();
				}// end try
				catch (Exception ex) {
					System.out.println("Update exception in ProductLine db: " + ex);
				}
				return (rc);
			}

			public int delete(Int productId) {
				int rc = -1;

				String query = "DELETE FROM ProductLine WHERE No = '" + productId + "'";
				System.out.println(query);
				try { // delete from ProductLine
					Statement stmt = con.createStatement();
					stmt.setQueryTimeout(5);
					rc = stmt.executeUpdate(query);
					stmt.close();
				}// end try
				catch (Exception ex) {
					System.out.println("Delete exception in ProductLine db: " + ex);
				}
				return (rc);
			}

			// private methods
			// michWere is used whenever we want to select more than one ProductLine

			private ArrayList<ProductLine> miscWhere(String wClause,
					boolean retrieveAssociation) {
				ResultSet results;
				ArrayList<ProductLine> list = new ArrayList<ProductLine>();

				String query = buildQuery(wClause);

				try { // read the ProductLine from the database
					Statement stmt = con.createStatement();
					stmt.setQueryTimeout(5);
					results = stmt.executeQuery(query);

					while (results.next()) {
						ProductLine productLnObj = new ProductLine();
						productLnObj = buildProductLine(results);
						if (retrieveAssociation) {
							IFDbProducts DbProdObj = new DbProduct();
	                        Product prodObj = DbProdObj.searchProductId(productLnObj.getProduct().getProductId(), false);
	                        System.out.println("Product is selected ");
	                        productLnObj.setProduct(prodObj);
							System.out.println("SalesLines are selected");
						}
						list.add(productLnObj);
					}// end while
					stmt.close();

				}// end try
				catch (Exception e) {
					System.out.println("Query exception - select: " + e);
					e.printStackTrace();
				}
				return list;
			}

			// SingleWhere is used when we only select one ProductLine
			private ProductLine singleWhere(String wClause, boolean retrieveAssociation) {
				ResultSet results;
				ProductLine productLnObj = new ProductLine();

				String query = buildQuery(wClause);
				System.out.println(query);
				try { // read the ProductLine from the database
					Statement stmt = con.createStatement();
					stmt.setQueryTimeout(5);
					results = stmt.executeQuery(query);

					if (results.next()) {
						productLnObj = buildProductLine(results);
						stmt.close();
						if (retrieveAssociation) {
							IFDbProducts DBProdObj = new DbProduct();
	                        Product prodObj = DBProdObj.searchProductId(productLnObj.getProduct().getProductId(), false);
	                        productLnObj.setProduct(prodObj);
	                        System.out.println("Product is selected ");
						}
						
					} else { // no ProductLine was found
						productLnObj = null;
					}
				}// end try
				catch (Exception e) {
					System.out.println("Query exception: " + e);
				}
				return productLnObj;
			}

			// method to build the query
			private String buildQuery(String wClause) {
				String query = "SELECT ProductId, quantity, FROM ProductLine";

				if (wClause.length() > 0)
					query = query + " WHERE " + wClause;

				return query;
			}

			// method to build a ProductLine object
			
}
			
